/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package travelexperts;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author 618325
 */
public class DBAccess 
{
       Connection conn;
       Statement stmt;
     
       ArrayList arrayListData = null;
       
    //Defining constructor and setting connection   
       
       public static void main(String args[]) throws SQLException {
           
           // This method is only for testing purpose
           
           DBAccess objDb = new DBAccess();
           
           ArrayList<Agent_Data> selectAgenciesData = objDb.selectAgentData();
           System.out.println("DATA:"+selectAgenciesData.toString());
           
        
           DBAccess objDb2 = new DBAccess();
           ArrayList<Agencies_Data> selectAgenciesData2 = objDb2.selectAgenciesData();
           System.out.println("DATA:"+selectAgenciesData2.toString());
           
           DBAccess objDb3 = new DBAccess();
           ArrayList<Customers_Data> selectAgenciesData3 = objDb3.selectCustomersData();
           System.out.println("DATA:"+selectAgenciesData3.toString());
           
           DBAccess objDb4 = new DBAccess();
           ArrayList<Packages_Data> selectAgenciesData4 = objDb4.selectPkgData();
           System.out.println("DATA:"+selectAgenciesData4.toString());
           
           DBAccess objDb5 = new DBAccess();
           ArrayList<PkgProSup_Data> selectAgenciesData5 = objDb5.selectPkgProSupData();
           System.out.println("DATA:"+selectAgenciesData5.toString());
           
           DBAccess objDb6 = new DBAccess();
           ArrayList<ProdSup_Data> selectAgenciesData6 = objDb6.selectProdSupData();
           System.out.println("DATA:"+selectAgenciesData6.toString());
           
           DBAccess objDb7 = new DBAccess();
           ArrayList<Products_Data> selectAgenciesData7 = objDb7.selectProductData();
           System.out.println("DATA:"+selectAgenciesData7.toString());
           
           DBAccess objDb8 = new DBAccess();
           ArrayList<Suppliers_Data> selectAgenciesData8 = objDb8.selectSupplierData();
           System.out.println("DATA:"+selectAgenciesData8.toString());
            
           DBAccess objDb9 = new DBAccess();
           Agent_Data agtDat = new Agent_Data();
           agtDat.setAgtId("13");
           agtDat.setAgtFirstNm("sam");
           agtDat.setAgtLastNm("liu");
           agtDat.setAgencyId("3");
         //objDb9.deleteAgtData(agtDat);
        // objDb9.insertAgtData(agtDat);
          // System.out.println("DATA:"+selectAgenciesData8.toString());
            
           /*
            //update testing agent class
           Agent_Data agtDat = new Agent_Data();
           agtDat.setAgtId("9");
           agtDat.setAgtFirstNm("JC");
           agtDat.setAgtLastNm("C");
           agtDat.setAgencyId("3");
           DBAccess objDb9 = new DBAccess();
           objDb9.updateAgtData(agtDat);
           
           */
        }
    public DBAccess()
    {
        super();
        //setting connection and connection string
         
        try 
        {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/travelexperts?zeroDateTimeBehavior=convertToNull", "root", "");
            stmt = conn.createStatement();
        } 
        catch (SQLException ex)
        {
            System.out.println("Exception details: "+ex.getMessage());
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /*
     * SELECTION OF ALL TABLE DATA
     */
    
        //Getting agent tabel data
           public ArrayList<Agent_Data> selectActiveAgentData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM agents WHERE AgtPosition<>'Inactive'");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agent_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Agent_Data objAgt = new Agent_Data();
                       
                            objAgt.setAgtId(result.getString("AgentId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objAgt.setAgtFirstNm(result.getString("AgtFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objAgt.setAgtMiddleNm(result.getString("AgtMiddleInitial"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objAgt.setAgtLastNm(result.getString("AgtLastName"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objAgt.setAgtBusPhone(result.getString("AgtBusPhone"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objAgt.setAgtEmail(result.getString("AgtEmail"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objAgt.setAgtPosition(result.getString("AgtPosition"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objAgt.setAgencyId(result.getString("AgencyId"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                        arrayListData.add(objAgt);
                        
                    
                }
            }

       
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        public ArrayList<Agent_Data> selectInactiveAgentData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM agents WHERE AgtPosition='Inactive'");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agent_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Agent_Data objAgt = new Agent_Data();
                       
                            objAgt.setAgtId(result.getString("AgentId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objAgt.setAgtFirstNm(result.getString("AgtFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objAgt.setAgtMiddleNm(result.getString("AgtMiddleInitial"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objAgt.setAgtLastNm(result.getString("AgtLastName"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objAgt.setAgtBusPhone(result.getString("AgtBusPhone"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objAgt.setAgtEmail(result.getString("AgtEmail"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objAgt.setAgtPosition(result.getString("AgtPosition"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objAgt.setAgencyId(result.getString("AgencyId"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                        arrayListData.add(objAgt);
                        
                    
                }
            }

       
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
    public ArrayList<Agent_Data> selectAgentData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM agents");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agent_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Agent_Data objAgt = new Agent_Data();
                       
                            objAgt.setAgtId(result.getString("AgentId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objAgt.setAgtFirstNm(result.getString("AgtFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objAgt.setAgtMiddleNm(result.getString("AgtMiddleInitial"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objAgt.setAgtLastNm(result.getString("AgtLastName"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objAgt.setAgtBusPhone(result.getString("AgtBusPhone"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objAgt.setAgtEmail(result.getString("AgtEmail"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objAgt.setAgtPosition(result.getString("AgtPosition"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objAgt.setAgencyId(result.getString("AgencyId"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                        arrayListData.add(objAgt);
                        
                    
                }
            }

       
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
    
     //Getting agency tabel data
        public ArrayList<Agencies_Data> selectAgenciesData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM agencies");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agencies_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Agencies_Data objAgency = new Agencies_Data();
                       
                            objAgency.setAgencyId(result.getString("AgencyId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objAgency.setAgncyAddress(result.getString("AgncyAddress"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objAgency.setAgncyCity(result.getString("AgncyCity"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objAgency.setAgncyProv(result.getString("AgncyProv"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objAgency.setAgncyPostal(result.getString("AgncyPostal"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objAgency.setAgncyCountry(result.getString("AgncyCountry"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objAgency.setAgncyPhone(result.getString("AgncyPhone"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objAgency.setAgncyFax(result.getString("AgncyFax"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                        arrayListData.add(objAgency);
                        
                    
                }
            }


        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
       //Getting Customer tabel data
        public ArrayList<Customers_Data> selectCustomersData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM customers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agencies_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Customers_Data objCust = new Customers_Data();
                       
                            objCust.setCustomerId(result.getInt("CustomerId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objCust.setCustFirstName(result.getString("CustFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objCust.setCustLastName(result.getString("CustLastName"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objCust.setCustAddress(result.getString("CustAddress"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objCust.setCustCity(result.getString("CustCity"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objCust.setCustProv(result.getString("CustProv"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objCust.setCustPostal(result.getString("CustPostal"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objCust.setCustCountry(result.getString("CustCountry"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                            objCust.setCustHomePhone(result.getString("CustHomePhone"));
                            objCust.setCustBusPhone(result.getString("CustBusPhone"));
                            objCust.setCustEmail(result.getString("CustEmail"));
                            objCust.setAgentId(result.getInt("AgentId"));
                        arrayListData.add(objCust);
                        
                    
                }
            }

            
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
    
    public ArrayList<Customers_Data> selectCustomersData(String agtId) throws SQLException
    {
     String sqlStr="";
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (agtId=="")
        { 
            sqlStr="SELECT * FROM customers";
        }
        else{
             sqlStr="SELECT * FROM customers WHERE AgentId="+agtId;
        }
        try
        {
            
            ResultSet result = stmt.executeQuery(sqlStr);
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Agencies_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Customers_Data objCust = new Customers_Data();
                       
                            objCust.setCustomerId(result.getInt("CustomerId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objCust.setCustFirstName(result.getString("CustFirstName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objCust.setCustLastName(result.getString("CustLastName"));
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objCust.setCustAddress(result.getString("CustAddress"));
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objCust.setCustCity(result.getString("CustCity"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objCust.setCustProv(result.getString("CustProv"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objCust.setCustPostal(result.getString("CustPostal"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));
                            objCust.setCustCountry(result.getString("CustCountry"));
                       //System.out.println("AgencyId:-"+result.getString("AgencyId"));
                            objCust.setCustHomePhone(result.getString("CustHomePhone"));
                            objCust.setCustBusPhone(result.getString("CustBusPhone"));
                            objCust.setCustEmail(result.getString("CustEmail"));
                            objCust.setAgentId(result.getInt("AgentId"));
                        arrayListData.add(objCust);
                        
                    
                }
            }

            
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        //Getting Packages tabel data
        public ArrayList<Packages_Data> selectPkgData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM packages");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Packages_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Packages_Data objPkg = new Packages_Data();
                       
                            objPkg.setPackageId(result.getInt("PackageId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPkg.setPkgName(result.getString("PkgName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objPkg.setPkgStartDate(result.getDate("PkgStartDate").toString());
                           //  System.out.println("AgtMiddleInitial:-"+result.getString("AgtMiddleInitial"));
                            objPkg.setPkgEndDate(result.getDate("PkgEndDate").toString());
                           // System.out.println("AgtLastName:-"+result.getString("AgtLastName"));
                            objPkg.setPkgDesc(result.getString("PkgDesc"));
                          //  System.out.println("AgtBusPhone:-"+result.getString("AgtBusPhone"));
                            objPkg.setPkgBasePrice(result.getBigDecimal("PkgBasePrice"));
                           //   System.out.println("AgtEmail:-"+result.getString("AgtEmail"));
                            objPkg.setPkgAgencyCommission(result.getBigDecimal("PkgAgencyCommission"));
                           // System.out.println("AgtPosition:-"+result.getString("AgtPosition"));

                        arrayListData.add(objPkg);
                        
                    
                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }      
        
        
        //Getting Product tabel data
        public ArrayList<Products_Data> selectProductData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM products");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Products_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Products_Data objPro = new Products_Data();
                       
                            objPro.setProductId(result.getInt("ProductId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPro.setProductName(result.getString("ProdName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));

                        arrayListData.add(objPro);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
    public ArrayList<ProdSup_Data> selectProductSupplierDataInPkg(String pkgId,boolean equalorNot) throws SQLException
    {
     int id=0;
     String equalId;
     if (equalorNot)
     {
         equalId="=";
     }
     else
     {
         equalId="<>";
     }
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            if (!"".equals(pkgId))
            {
                id=Integer.parseInt(pkgId);
            }
            ResultSet result = stmt.executeQuery("select ps.ProductSupplierId,ps.ProductId,ps.SupplierId,p.ProdName,s.SupName " +
"            from suppliers s,products p, products_suppliers ps,packages_products_suppliers pps,packages pkg " +
"            where s.SupplierId=ps.SupplierId and p.ProductId=ps.ProductId and ps.ProductSupplierId=pps.ProductSupplierId " +
"            and pps.PackageId=pkg.PackageId and pkg.PackageId"+equalId+id);
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<ProdSup_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        ProdSup_Data objPro = new ProdSup_Data();
                       
                            objPro.setProductId(result.getInt("ProductId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPro.setProdName(result.getString("ProdName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objPro.setSuppName(result.getString("SupName"));
                            objPro.setSupplierId(result.getInt("SupplierId"));
                            objPro.setProductSupplierId(result.getInt("ProductSupplierId"));
                        arrayListData.add(objPro);

                }
            }

            
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    } 
    
        public ArrayList<ProdSup_Data> selectProductSupplierDataNotInPkg() throws SQLException
    {
     int id=0;
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            ResultSet result = stmt.executeQuery("select ps.ProductSupplierId,ps.ProductId,ps.SupplierId,p.ProdName,s.SupName "
                    +"from suppliers s,products p, products_suppliers ps "
                    +"where s.SupplierId=ps.SupplierId and p.ProductId=ps.ProductId ");
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<ProdSup_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        ProdSup_Data objPro = new ProdSup_Data();
                       
                            objPro.setProductId(result.getInt("ProductId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPro.setProdName(result.getString("ProdName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objPro.setSuppName(result.getString("SupName"));
                            objPro.setSupplierId(result.getInt("SupplierId"));
                            objPro.setProductSupplierId(result.getInt("ProductSupplierId"));
                        arrayListData.add(objPro);

                }
            }

            
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
          //Getting Supplier tabel data
        public ArrayList<Suppliers_Data> selectSupplierData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM suppliers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<Suppliers_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        Suppliers_Data objSup = new Suppliers_Data();
                       
                            objSup.setSupplierId(result.getInt("SupplierId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objSup.setSupName(result.getString("SupName"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));

                        arrayListData.add(objSup);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
              //Getting packages_products_suppliers tabel data
        public ArrayList<PkgProSup_Data> selectPkgProSupData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM packages_products_suppliers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<PkgProSup_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        PkgProSup_Data objPkgProsup = new PkgProSup_Data();
                       
                            objPkgProsup.setPackageId(result.getInt("PackageId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objPkgProsup.setProductSupplierId(result.getInt("ProductSupplierId"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));

                        arrayListData.add(objPkgProsup);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
        //Getting products_suppliers tabel data
        public ArrayList<ProdSup_Data> selectProdSupData() throws SQLException
    {
     
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            
            ResultSet result = stmt.executeQuery("SELECT * FROM products_suppliers");
            
            ResultSetMetaData rs= result.getMetaData();
            int numCols = rs.getColumnCount();
           
            if(result != null)
            {
               
                arrayListData = new ArrayList<ProdSup_Data>();
                //iterating through data
                 while(result.next())
                {
               
                        ProdSup_Data objProsup = new ProdSup_Data();
                       
                            objProsup.setProductId(result.getInt("ProductId"));
                           // System.out.println("AgentId:-"+result.getString("AgentId"));
                            objProsup.setProductSupplierId(result.getInt("ProductSupplierId"));
                          //  System.out.println("AgtFirstName:-"+result.getString("AgtFirstName"));
                            objProsup.setSupplierId(result.getInt("SupplierId")); 
                            
                        arrayListData.add(objProsup);

                }
            }

           
        }
        catch(Exception e)
        {
            //catching connection
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
        finally
        {
             //closing connection
            conn.close();
        }
       return arrayListData;
 
    }
        
      /*
     * METHODS TO UPDATE TABLE DATA
     */
        
        public void updateAgtData(Agent_Data agtData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE agents SET "
                    + "AgtFirstName='"+agtData.getAgtFirstNm()+"'"
                    + ",AgtMiddleInitial='"+agtData.getAgtMiddleNm()+"'"
                    + ",AgtLastName='"+agtData.getAgtLastNm()+"'"
                    + ",AgtBusPhone='"+agtData.getAgtBusPhone()+"'"
                    + ",AgtEmail='"+agtData.getAgtEmail()+"'"
                    + ",AgtPosition='"+agtData.getAgtPosition()+"'"
                    + ",AgencyId='"+agtData.getAgencyId()+"'"
                    + "WHERE AgentId="+agtData.getAgtId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Update is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
        public void updateAgencyData(Agencies_Data agencyData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE agencies SET "
                    + "AgncyFax='"+agencyData.getAgncyFax()+"'"
                    + ",AgncyAddress='"+agencyData.getAgncyAddress()+"'"
                    + ",AgncyCity='"+agencyData.getAgncyCity()+"'"
                    + ",AgncyProv='"+agencyData.getAgncyProv()+"'"
                    + ",AgncyPostal='"+agencyData.getAgncyPostal()+"'"
                    + ",AgncyCountry='"+agencyData.getAgncyCountry()+"'"
                    + ",AgncyPhone='"+agencyData.getAgncyPhone()+"'"
                    + "WHERE AgencyId="+agencyData.getAgencyId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
    
      public void updateCustomersData(Customers_Data custData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE customers SET "
                    + "CustFirstName='"+custData.getCustFirstName()+"'"
                    + ",CustLastName='"+custData.getCustLastName()+"'"
                    + ",CustAddress='"+custData.getCustAddress()+"'"
                    + ",CustCity='"+custData.getCustCity()+"'"
                    + ",CustProv='"+custData.getCustProv()+"'"
                    + ",CustPostal='"+custData.getCustPostal()+"'"
                    + ",CustCountry='"+custData.getCustCountry()+"'"
                    + ",CustHomePhone='"+custData.getCustHomePhone()+"'"
                    + ",CustBusPhone='"+custData.getCustBusPhone()+"'"
                     + ",CustEmail='"+custData.getCustEmail()+"'"  
                    + ",AgentId='"+custData.getAgentId()+"'" 
                    + "WHERE CustomerId="+custData.getCustomerId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
      public void updatePackagesData(Packages_Data pkgData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE packages SET "
                    + "PkgName='"+pkgData.getPkgName()+"'"
                    + ",PkgStartDate='"+pkgData.getPkgStartDate()+"'"
                    + ",PkgEndDate='"+pkgData.getPkgEndDate()+"'"
                    + ",PkgDesc='"+pkgData.getPkgDesc()+"'"
                    + ",PkgBasePrice='"+pkgData.getPkgBasePrice()+"'"
                    + ",PkgAgencyCommission='"+pkgData.getPkgAgencyCommission()+"'"                   
                    + "WHERE PackageId="+pkgData.getPackageId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
      public void updatePackagesProSupData(PkgProSup_Data pkgProSupData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE packages_products_suppliers SET "
                    + "PackageId='"+pkgProSupData.getPackageId()+"'"                  
                    + "WHERE ProductSupplierId="+pkgProSupData.getProductSupplierId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
           
      public void updateProdSupData(ProdSup_Data prodSupData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE products_suppliers SET "
                    + "ProductId='"+prodSupData.getProductId()+"'"    
                    + "SupplierId='"+prodSupData.getSupplierId()+"'"
                    + "WHERE ProductSupplierId="+prodSupData.getProductSupplierId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
       public void updateProducts(Products_Data prodData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE products SET "
                    + "ProdName='"+prodData.getProductName()+"'"
                    + "WHERE ProductId="+prodData.getProductId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
             
       public void updateSuppliers(Suppliers_Data supData)
    {
        int intUpdateRes = -1; //setting update variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
           
            //creating update statement   
            intUpdateRes =stmt.executeUpdate("UPDATE suppliers SET "
                    + "SupName='"+supData.getSupName()+"'"
                    + "WHERE SupplierId="+supData.getSupplierId()+"");
                    
            System.out.println("UPDATE RESULT:"+intUpdateRes);
            
            if (intUpdateRes==0) 
            {
                //update failed
            JOptionPane.showMessageDialog(null, "Update is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats, successfully updated.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
       
              
     /*
     * METHODS TO INSERT DATA
     */
       
       //insert into agent tabel
      public void insertAgtData(Agent_Data agtData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO agents VALUES("
                    +"'"+agtData.getAgtId()
                    + "','"+agtData.getAgtFirstNm()
                    + "','"+agtData.getAgtMiddleNm()
                    + "','"+agtData.getAgtLastNm()
                    + "','"+agtData.getAgtBusPhone()
                    + "','"+agtData.getAgtEmail()
                    + "','"+agtData.getAgtPosition()
                    + "','"+agtData.getAgencyId()+"')";
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
 
      //insert into agency tabel
     public void insertAgencyData(Agencies_Data agncyData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO agencies VALUES("
                    +"'"+agncyData.getAgencyId()
                    + "','"+agncyData.getAgncyAddress()
                    + "','"+agncyData.getAgncyCity()
                    + "','"+agncyData.getAgncyProv()
                    + "','"+agncyData.getAgncyPostal()
                    + "','"+agncyData.getAgncyCountry()
                    + "','"+agncyData.getAgncyPhone()
                    + "','"+agncyData.getAgncyFax()+"')";
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
     
        //insert into customer tabel
     public void insertCustomerData(Customers_Data custData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO customers VALUES("
                    +"'"+custData.getCustomerId()
                    + "','"+custData.getCustFirstName()
                    + "','"+custData.getCustLastName()
                    + "','"+custData.getCustAddress()
                    + "','"+custData.getCustCity()
                    + "','"+custData.getCustProv()
                    + "','"+custData.getCustPostal()
                    + "','"+custData.getCustCountry()
                    + "','"+custData.getCustHomePhone()
                    + "','"+custData.getCustBusPhone()
                    + "','"+custData.getCustEmail()+"')";
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
    
        //insert into packages tabel
     public void insertPkgsData(Packages_Data pkgData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO packages VALUES("
                    +"'"+pkgData.getPackageId()
                    + "','"+pkgData.getPkgName()
                    + "','"+pkgData.getPkgStartDate()
                    + "','"+pkgData.getPkgEndDate()
                    + "','"+pkgData.getPkgDesc()
                    + "','"+pkgData.getPkgBasePrice()
                    + "','"+pkgData.getPkgAgencyCommission()+"')";
                      
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
           
        //insert into packages_products_suppliers tabel
     public void insertPkgPoSuprData(PkgProSup_Data pkgProSupData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO packages_products_suppliers VALUES("
                    +"'"+pkgProSupData.getPackageId()
                    + "','"+pkgProSupData.getProductSupplierId()+"')";
                      
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
      //insert into products_suppliers tabel
     public void insertProSuprData(ProdSup_Data proSupData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO products_suppliers VALUES("
                    +"'"+proSupData.getProductSupplierId()
                    +"'"+proSupData.getProductId()
                    + "','"+proSupData.getSupplierId()+"')";
                      
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
     
      //insert into products tabel
     public void insertProductData(Products_Data proData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO products VALUES("
                    +"'"+proData.getProductId()
                    + "','"+proData.getProductName()+"')";
                      
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
     
      //insert into suppliers tabel
     public void insertSupplierData(Suppliers_Data proData)
    {
        int intInsertRes = -1; //setting insert variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //creating INSERT statement   
            
                      String sql = "INSERT INTO suppliers VALUES("
                    +"'"+proData.getSupplierId()
                    + "','"+proData.getSupName()+"')";
                      
                      System.out.println(sql);
            intInsertRes =stmt.executeUpdate(sql);
                    
            //
            System.out.println("INSERT RESULT:"+intInsertRes);
            
            if (intInsertRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Insert is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Insert is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
     
     /*
     * METHODS TO DELETE DATA
     */
      
     //deleting agents by their id
      public void deleteAgtData(Agent_Data agtData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM agents WHERE AgentId ="+agtData.getAgtId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
          
     //deleting agency by their id
      public void deleteAgenciData(Agencies_Data agncyData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM agencies WHERE AgencyId ="+agncyData.getAgencyId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
           
     //deleting customer by their id
      public void deleteCustomerData(Customers_Data custData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM customers WHERE CustomerId ="+custData.getCustomerId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
       
     //deleting packages by their id
      public void deletePkgData(Packages_Data packData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM packages WHERE PackageId ="+packData.getPackageId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
     //deleting packages_products_suppliers by their id
      public void deletePkgProSupData(PkgProSup_Data packData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM packages_products_suppliers WHERE PackageId ="+packData.getPackageId()+" AND ProductSupplierId="+packData.getProductSupplierId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
        
     //deleting products_suppliers by their id
      public void deleteProSupData(ProdSup_Data proSupData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM products_suppliers WHERE ProductSupplierId ="+proSupData.getProductSupplierId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
          
     //deleting products by their id
      public void deleteProductsData(Products_Data prodsData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM products WHERE ProductId ="+prodsData.getProductId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
      
        
     //deleting suppliers by their id
      public void deleteSuppliersData(Suppliers_Data supsData)
    {
        int intDeletedRes = 0; //setting delete variable to -1
        try 
        {
            Class.forName("com.mysql.jdbc.Driver");
        } 
        catch (ClassNotFoundException ex) {
            System.out.println("FAILED TO LOAD MY SQL DRIVER.");
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        { 
            
            //DELETE SQL statement   
            
                      String sql = "DELETE FROM suppliers WHERE SupplierId ="+supsData.getSupplierId();
                       System.out.println(sql);
            
                       intDeletedRes =stmt.executeUpdate(sql);
                    
            System.out.println("DELETE RESULT:"+intDeletedRes);
            
            if (intDeletedRes==0) 
            {
                //insertion failed
            JOptionPane.showMessageDialog(null, "Delete is unsuccessful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                 JOptionPane.showMessageDialog(null, "Congrats.Delete is successful.", "InfoBox", JOptionPane.INFORMATION_MESSAGE);
            
            }
            //closing connection
            conn.close();
        }
        catch(Exception e)
        {
            //catching exception
            System.out.println("Exception occured. Reason:");
             e.printStackTrace();
        }
      
    }
}
